﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using BCCommon;
using BCEntity.MySql.MaterialConfiguration;
using Dapper;

namespace BCData.MySql.MaterialConfiguration
{
    public class MaterialConfigurationData : IMaterialConfigurationData
    {
        #region 私有变量

        private const string QUERY_SQL = @"SELECT MaterialConfigurationId,MaterialName,MaterialType,Address,Content,Remark,
                                           RemarkPhoto,UsageGroupCode,AllowDelete,CreateTime,UpdateTime,Editer 
                                           FROM Materialconfiguration";
        protected readonly IDatabaseContext database;

        #endregion

        #region 构造函数
        public MaterialConfigurationData(IDatabaseContext command)
        {
            this.database = command;
        }
        #endregion

        #region 公共方法
        public MaterialConfigurationEntity Create(MaterialConfigurationEntity materialConfigurationEntity)
        {
            return database.Master.InsertAsync(materialConfigurationEntity).GetAwaiter().GetResult() ;
        }

        public int Delete(int materialConfigurationId)
        {
            string deleteSql = "DELETE FROM materialconfiguration where MaterialConfigurationId=?MaterialConfigurationId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MaterialConfigurationId", materialConfigurationId, DbType.Int32, size: 11);
            return database.Master.Execute(deleteSql, parameters);
        }

        public bool Exists(string materialName)
        {
            string sql = @"SELECT 1 FROM materialconfiguration WHERE MaterialName=?MaterialName;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MaterialName", materialName, DbType.AnsiString, size: 30);
            object result = this.database.Slave.ExecuteScalar(sql,  parameters);
            return result != null; ;
        }

        public bool Exists(int materialConfigurationId)
        {
            string sql = @"SELECT 1 FROM Materialconfiguration WHERE MaterialConfigurationId=?MaterialConfigurationId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MaterialConfigurationId", materialConfigurationId, DbType.Int32, size: 11);
            object result = this.database.Slave.ExecuteScalar(sql,  parameters);
            return result != null;
        }

        public MaterialConfigurationEntity Get(int materialConfigurationId)
        {
            string sql = @"{0} WHERE MaterialConfigurationId=?MaterialConfigurationId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MaterialConfigurationId", materialConfigurationId, DbType.Int32, size: 20);
            return this.database.Slave.QueryEntity<MaterialConfigurationEntity>(string.Format(sql, QUERY_SQL),  parameters);
        }
        public MaterialConfigurationEntity Get(string searchKey)
        {
            string sql = @"SELECT A.MaterialConfigurationId,A.MaterialName,A.MaterialType,A.Address,A.Content,A.Remark,
                                A.RemarkPhoto,A.UsageGroupCode,A.AllowDelete,A.CreateTime,A.UpdateTime,A.Editer 
                            FROM Materialconfiguration A
                                INNER JOIN DictionaryReference B ON B.SearchKey=A.UsageGroupCode
                            WHERE B.Name=?SearchKey;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SearchKey", searchKey, DbType.AnsiString, size: 150);
            return this.database.Slave.QueryEntity<MaterialConfigurationEntity>(sql, parameters);
        }
        public IEnumerable<MaterialConfigurationEntity> List(string materialName, string materialType, string remark, string usageGroupCode, DateTime? start, DateTime? end, bool? ascending, out int dataCount, int pageIndex = 1, int pageSize = 10)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(materialName))
            {
                wheres.Add("MaterialName=?MaterialName");
                parameters.Add("?MaterialName", materialName, DbType.AnsiString, size: 30);
            }
            if (!string.IsNullOrWhiteSpace(materialType))
            {
                wheres.Add("MaterialType=?MaterialType");
                parameters.Add("?MaterialType", materialType, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(remark))
            {
                wheres.Add("Remark=?Remark");
                parameters.Add("?Remark", remark, DbType.AnsiString, size: 200);
            }
            if (!string.IsNullOrWhiteSpace(usageGroupCode))
            {
                wheres.Add("UsageGroupCode=?UsageGroupCode");
                parameters.Add("?UsageGroupCode", usageGroupCode, DbType.AnsiString, size: 50);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY MaterialConfigurationId {4} LIMIT {2},{3}; 
            SELECT Count(1) FROM Materialconfiguration {1}",
                  QUERY_SQL,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            var result = this.database.QueryMultiple<MaterialConfigurationEntity, int>(sql, parameters);
            dataCount = result.Item2;
            return result.Item1;
        }

        public MaterialConfigurationEntity Update(MaterialConfigurationEntity materialConfigurationEntity)
        {
            string updateSql = @"UPDATE materialconfiguration SET                            
                            MaterialName = ?MaterialName,
                            MaterialType = ?MaterialType,
                            Address = ?Address,
                            Content = ?Content,
                            Remark = ?Remark,
                            RemarkPhoto = ?RemarkPhoto,
                            UsageGroupCode=?UsageGroupCode,
                            UpdateTime = ?UpdateTime,
                            Editer = ?Editer
                            WHERE MaterialConfigurationId=?MaterialConfigurationId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MaterialName", materialConfigurationEntity.MaterialName, DbType.AnsiString, size: 30);
            parameters.Add("?MaterialType", materialConfigurationEntity.MaterialType.ToString(), DbType.AnsiString);
            parameters.Add("?Address", materialConfigurationEntity.Address, DbType.AnsiString, size: 1000);
            parameters.Add("?Content", materialConfigurationEntity.Content, DbType.String);
            parameters.Add("?Remark", materialConfigurationEntity.Remark, DbType.AnsiString, size: 200);
            parameters.Add("?RemarkPhoto", materialConfigurationEntity.RemarkPhoto, DbType.AnsiString, size: 1000);
            parameters.Add("?UsageGroupCode", materialConfigurationEntity.UsageGroupCode, DbType.AnsiString, size: 150);
            parameters.Add("?UpdateTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?Editer", materialConfigurationEntity.Editer, DbType.AnsiString, size: 20);
            parameters.Add("?MaterialConfigurationId", materialConfigurationEntity.MaterialConfigurationId, DbType.Int32, size: 11);
            string sql = string.Format(@"{0} {1} Where MaterialConfigurationId=?MaterialConfigurationId;", updateSql, QUERY_SQL);
            var result = this.database.Master.QueryEntityAsync<MaterialConfigurationEntity>(sql,  parameters);
            return result.GetAwaiter().GetResult();
        }
        #endregion
    }
}
